package com.sophossystems.client.asmon.dao.spring;

import java.sql.SQLException;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.transaction.annotation.Transactional;

import com.sophossystems.client.asmon.dao.ServiciosDao;
import com.sophossystems.client.asmon.dto.ServiciosDTO;
import com.sophossystems.client.asmon.dto.ServiciosPkDTO;
import com.sophossystems.client.asmon.exceptions.DaoException;
import com.sophossystems.client.asmon.hibernate.Servicios;

public class ServiciosDaoImpl extends AbstractDAO implements ServiciosDao {

	
	private static Logger log = Logger.getLogger(ServiciosDaoImpl.class);
	/**
	 * Method 'insert'
	 * 
	 * @param dto
	 * @return ActivityPk
	 */
	@Override
	@Transactional
	public ServiciosPkDTO insert(final ServiciosDTO dto) {
		ServiciosPkDTO exito = null;
		try {
			exito = (ServiciosPkDTO) getHibernateTemplate().execute(
					new HibernateCallback() {
						/**
						 * {@inheritDoc}
						 * 
						 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
						 */
						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							Transaction tx = session.beginTransaction();
							ServiciosPkDTO pk = null;
							Servicios servicios = new Servicios();
							servicios.mapper(dto);
							session.save(servicios);
							tx.commit();

							try {
								ServiciosPkDTO res = findLastInsert();
								pk = new ServiciosPkDTO();
								pk.setIdServicos(res.getIdServicos());
							} catch (DaoException es) {

								throw new HibernateException("Insersion: "
										+ es.getMessage());
							}
							return pk;

						}

					});
			// / return exito;
		} catch (final Exception e) {
			logger.error("Error: getActivity, cause: " + e.getMessage(), e);

		}
		return exito;
	}

	/**
	 * Updates a single row in the Activity table.
	 */
	@Override
	@Transactional
	public void update(final ServiciosDTO dto) throws DaoException {

		try {
			getHibernateTemplate().execute(new HibernateCallback() {
				/**
				 * {@inheritDoc}
				 * 
				 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
				 */
				@Override
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					ServiciosPkDTO pk = null;
					ServiciosDTO res = null;
					Transaction tx = session.beginTransaction();
					// Aqui primero se busca si existe el registro
					try {
						res = findByPrimaryKey(dto
								.getIdServicos());
						pk = new ServiciosPkDTO();
						pk.setIdServicos(res.getIdServicos());
					} catch (DaoException es) {
						throw new HibernateException("Update ServiciosDao: "
								+ es.getMessage());
					}
					// /aqui se actualizar los campos y con los de la entrada
					// cuidado de actualiza todo.
					Servicios persis = new Servicios();
					persis.mapper(dto);
					persis.setIdServicos(res.getIdServicos());
					session.update(persis);
					tx.commit();
					return null;
				}

			});
			// / return exito;
		} catch (final Exception e) {
			logger.error("Error: getActivity, cause: " + e.getMessage(), e);

		}

	}

	/**
	 * Deletes a single row in the Activity table.
	 */
	@Override
	@Transactional
	public void delete(final ServiciosPkDTO pk) throws DaoException {

		try {
			getHibernateTemplate().execute(new HibernateCallback() {
				/**
				 * {@inheritDoc}
				 * 
				 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
				 */
				@Override
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					Transaction tx = session.beginTransaction();
					Servicios servicios = (Servicios) session.load(
							Servicios.class, pk.getIdServicos());
					session.delete(servicios);
					tx.commit();
					return null;
				}
			});
		} catch (final Exception e) {
			logger.error("Error: updateServicios, cause: " + e.getMessage(), e);
			throw new DaoException("Error: updateServicios, cause: "
					+ e.getMessage());
		}
	}

	/**
	 * Method 'getTableName'
	 * 
	 * @return String
	 */
	public String getTableName() {
		return "Servicios";
	}

	/**
	 * Returns all rows from the SERVICIOS table that match the criteria
	 * 'idServicos = :idServicos'.
	 */
	@Override
	@Transactional
	public ServiciosDTO findByPrimaryKey(final int idServicos) throws DaoException {
		ServiciosDTO servicios = null;
		try {
			servicios = (ServiciosDTO) getHibernateTemplate().execute(
					new HibernateCallback() {

						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {

							Query oQuery = session
									.createQuery("SELECT s.idServicos as idServicos, s.idCliente as idCliente, s.altaShcp as altaShcp, s.altaImms as altaImms, s.reactivacion as reactivacion,"
											+ " s.actualizacion as actualizacion, s.erogaciones as erogaciones, s.fiscales as fiscales, s.otro as otro FROM "
											+ getTableName() + " s WHERE s.idServicos = :idServicios");
							oQuery.setInteger("idServicios", idServicos);
							ServiciosDTO serv = (ServiciosDTO) oQuery
									.setResultTransformer(
											Transformers
													.aliasToBean(ServiciosDTO.class))
									.uniqueResult();

							return serv;
						}
					});

		} catch (Exception e) {
			logger.error("Error: Servicios, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return servicios;
	}

	@Override
	@SuppressWarnings("unchecked")
	/** 
	 * Returns all rows from the SERVICIOS table that match the criteria ''.
	 */
	@Transactional
	public List<ServiciosDTO> findAll() throws DaoException {
		List<ServiciosDTO> servicios = null;
		try {
			servicios = (List<ServiciosDTO>) getHibernateTemplate().execute(
					new HibernateCallback() {

						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {

							Query oQuery = session
									.createQuery("SELECT s.idServicos as idServicos, s.idCliente as idCliente,"
											+ " s.altaShcp as altaShcp, s.altaImms as altaImms, s.reactivacion as"
											+ " reactivacion, s.actualizacion as actualizacion,"
											+ " s.erogaciones as erogaciones, s.fiscales as fiscales, s.otro as otro FROM "
											+ getTableName()
											+ " s ORDER BY s.idServicos");
							List<ServiciosDTO> lista = oQuery
									.setResultTransformer(
											Transformers
													.aliasToBean(ServiciosDTO.class))
									.list();
							return lista;
						}
					});
			// return
			// jdbcTemplate.query("SELECT idServicos, idCliente, altaDhcp, altaImms, reactivacion, actualizacion, erogaciones, fiscales FROM "
			// + getTableName() + " ORDER BY idServicos", this);
		} catch (Exception e) {
			logger.error("Error: Servicios, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return servicios;
	}

	/**
	 * Returns all rows from the SERVICIOS table that match the criteria
	 * 'idServicos = :idServicos'.
	 
	  @Transactional public List<ServiciosDTO> findWhereIdServicosEquals(int
	                idServicos) throws DaoException { try { return
	                 jdbcTemplate.query(
	 *                "SELECT idServicos, idCliente, altaDhcp, altaImms, reactivacion, actualizacion, erogaciones, fiscales FROM "
	 *                + getTableName() +
	 *                " WHERE idServicos = ? ORDER BY idServicos",
	 *                this,idServicos); } catch (Exception e) {
	 *                logger.error("Error: Servicios, cause: " + e.getMessage(),
	 *                e); throw new DaoException("Query failed", e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the SERVICIOS table that match the criteria
	 * 'idCliente = :idCliente'.
	 */
	@Override
	@SuppressWarnings("unchecked")
	@Transactional
	public List<ServiciosDTO> findWhereIdClienteEquals(final int idCliente)
			throws DaoException {
		List<ServiciosDTO> servicios = null;
		try {
			servicios = (List<ServiciosDTO>) getHibernateTemplate().execute(
					new HibernateCallback() {

						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {

							Query oQuery = session
									.createQuery("SELECT s.idServicos as idServicos, s.idCliente as idCliente, "
											+ "s.altaShcp as altaShcp, s.altaImms as altaImms, s.reactivacion as" 
											+ " reactivacion, s.actualizacion as actualizacion, s.erogaciones as erogaciones,"
											+ " s.fiscales as fiscales, s.otro as otro FROM "
											+ getTableName()
											+ " s WHERE s.idCliente = :id_cliente ORDER BY s.idCliente");
							oQuery.setInteger("id_cliente", idCliente);
							List<ServiciosDTO> lista = oQuery
									.setResultTransformer(
											Transformers
													.aliasToBean(ServiciosDTO.class))
									.list();
							return lista;
						}
					});

		} catch (Exception e) {
			logger.error("Error: Servicios, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return servicios;
	}

	/**
	 * Returns all rows from the SERVICIOS table that match the criteria
	 * 'altaDhcp = :altaDhcp'.
	 * 
	 * @Transactional public List<ServiciosDTO> findWhereAltaDhcpEquals(int
	 *                altaDhcp) throws DaoException { try { return
	 *                jdbcTemplate.query(
	 *                "SELECT idServicos, idCliente, altaDhcp, altaImms, reactivacion, actualizacion, erogaciones, fiscales FROM "
	 *                + getTableName() +
	 *                " WHERE altaDhcp = ? ORDER BY altaDhcp", this,altaDhcp); }
	 *                catch (Exception e) {
	 *                logger.error("Error: Servicios, cause: " + e.getMessage(),
	 *                e); throw new DaoException("Query failed", e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the SERVICIOS table that match the criteria
	 * 'altaImms = :altaImms'.
	 * 
	 * @Transactional public List<ServiciosDTO> findWhereAltaImmsEquals(int
	 *                altaImms) throws DaoException { try { return
	 *                jdbcTemplate.query(
	 *                "SELECT idServicos, idCliente, altaDhcp, altaImms, reactivacion, actualizacion, erogaciones, fiscales FROM "
	 *                + getTableName() +
	 *                " WHERE altaImms = ? ORDER BY altaImms", this,altaImms); }
	 *                catch (Exception e) {
	 *                logger.error("Error: Servicios, cause: " + e.getMessage(),
	 *                e); throw new DaoException("Query failed", e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the SERVICIOS table that match the criteria
	 * 'reactivacion = :reactivacion'.
	 * 
	 * @Transactional public List<ServiciosDTO> findWhereReactivacionEquals(int
	 *                reactivacion) throws DaoException { try { return
	 *                jdbcTemplate.query(
	 *                "SELECT idServicos, idCliente, altaDhcp, altaImms, reactivacion, actualizacion, erogaciones, fiscales FROM "
	 *                + getTableName() +
	 *                " WHERE reactivacion = ? ORDER BY reactivacion",
	 *                this,reactivacion); } catch (Exception e) {
	 *                logger.error("Error: Servicios, cause: " + e.getMessage(),
	 *                e); throw new DaoException("Query failed", e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the SERVICIOS table that match the criteria
	 * 'actualizacion = :actualizacion'.
	 * 
	 * @Transactional public List<ServiciosDTO> findWhereActualizacionEquals(int
	 *                actualizacion) throws DaoException { try { return
	 *                jdbcTemplate.query(
	 *                "SELECT idServicos, idCliente, altaDhcp, altaImms, reactivacion, actualizacion, erogaciones, fiscales FROM "
	 *                + getTableName() +
	 *                " WHERE actualizacion = ? ORDER BY actualizacion",
	 *                this,actualizacion); } catch (Exception e) {
	 *                logger.error("Error: Servicios, cause: " + e.getMessage(),
	 *                e); throw new DaoException("Query failed", e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the SERVICIOS table that match the criteria
	 * 'erogaciones = :erogaciones'.
	 * 
	 * @Transactional public List<ServiciosDTO> findWhereerogacionesEquals(int
	 *                erogaciones) throws DaoException { try { return
	 *                jdbcTemplate.query(
	 *                "SELECT idServicos, idCliente, altaDhcp, altaImms, reactivacion, actualizacion, erogaciones, fiscales FROM "
	 *                + getTableName() +
	 *                " WHERE erogaciones = ? ORDER BY erogaciones",
	 *                this,erogaciones); } catch (Exception e) {
	 *                logger.error("Error: Servicios, cause: " + e.getMessage(),
	 *                e); throw new DaoException("Query failed", e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the SERVICIOS table that match the criteria
	 * 'fiscales = :fiscales'.
	 * 
	 * @Transactional public List<ServiciosDTO> findWhereFiscalesEquals(int
	 *                fiscales) throws DaoException { try { return
	 *                jdbcTemplate.query(
	 *                "SELECT idServicos, idCliente, altaDhcp, altaImms, reactivacion, actualizacion, erogaciones, fiscales FROM "
	 *                + getTableName() +
	 *                " WHERE fiscales = ? ORDER BY fiscales", this,fiscales); }
	 *                catch (Exception e) {
	 *                logger.error("Error: Servicios, cause: " + e.getMessage(),
	 *                e); throw new DaoException("Query failed", e); }
	 * 
	 *                }
	 */

	/**
	 * Returns the rows from the SERVICIOS table that matches the specified
	 * primary-key value.
	 */
	@Override
	public ServiciosDTO findByPrimaryKey(ServiciosPkDTO pk) throws DaoException {
		return findByPrimaryKey(pk.getIdServicos());
	}

	@Override
	public ServiciosPkDTO findLastInsert() throws DaoException {
		ServiciosPkDTO servicios = null;
		try {
			servicios = (ServiciosPkDTO) getHibernateTemplate().execute(
					new HibernateCallback() {

						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {

							Query oQuery = session
									.createQuery("SELECT max(s.idServicos) as idServicos FROM "
											+ getTableName() + " s ");

							ServiciosPkDTO serviciosDto = (ServiciosPkDTO) oQuery
									.setResultTransformer(
											Transformers
													.aliasToBean(ServiciosPkDTO.class))
									.list().get(0);
							return serviciosDto;
						}
					});

		} catch (Exception e) {
			logger.error("Error: ServiciosDaoImpl, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return servicios;

	}

}
